package idv.arthur.work;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Calendar;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class Widgets {

	public String changeCode(String str) {
		String changedStr = "";
		try {
			changedStr = new String(str.getBytes("iso-8859-1"), "UTF-8");
		} catch (UnsupportedEncodingException e) {
			changedStr = e.toString();
		}
		return changedStr;
	}
	
	
	public String checkTRsubmittAble(int tpid,String mrid,int ver,Connection conn,PreparedStatement ps,ResultSet rs) {
		String res ="";
		
		String strSQL = "SELECT COUNT(*) a FROM tms_mrtcmapping WHERE tms_mrtcmapping.tpid=? AND tms_mrtcmapping.mrid=? AND tms_mrtcmapping.ver=?";
		
		try {
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, tpid);
			ps.setString(2, mrid);
			ps.setInt(3, ver);
			rs = ps.executeQuery();
			rs.next();
			int a = rs.getInt("a");
			strSQL = "SELECT COUNT(DISTINCT tcuid) FROM tms_mrtr WHERE  tms_mrtr.tpid = ? AND tms_mrtr.mrid = ? AND tms_mrtr.ver = ? AND trsn in (3,4,5)";
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, tpid);
			ps.setString(2, mrid);
			ps.setInt(3, ver);
			rs = ps.executeQuery();
			rs.next();
			
			int b = rs.getInt(1);
			
			/** 如果測試案例的數量(a) 等於 測試結果登打的數量(b) 則可以將測試結果送審 **/
			if (a==b) {
				strSQL = "UPDATE tms_tpitem SET isTRsubmitAble=1,lastUpdatedDate=? WHERE tpid=? AND mrid=?";
				res="1";
			} else {
				strSQL = "UPDATE tms_tpitem SET isTRsubmitAble=0,lastUpdatedDate=? WHERE tpid=? AND mrid=?";
				res="0";
			}
			ps = conn.prepareStatement(strSQL);
			ps.setTimestamp(1, currentTimeStamp());
			ps.setInt(2, tpid);
			ps.setString(3, mrid);
			ps.executeUpdate();
			
		} catch (SQLException e) {
			String errorMessage = "出問題\n";
			for (int i=0; i<e.getStackTrace().length;i++) {
				errorMessage += "FileName："+e.getStackTrace()[i].getFileName() + "\n";
				errorMessage += "LineNumber："+e.getStackTrace()[i].getLineNumber() + "\n";
				errorMessage += "MethodName："+e.getStackTrace()[i].getMethodName() + "\n";
				errorMessage += "ClassName："+e.getStackTrace()[i].getClassName() + "\n";
			}
			errorMessage += e.toString();
			res = errorMessage;
			System.out.println(errorMessage);
		}
		
		
		return res;
	}
	
	public User checkUserId(String str) throws SQLException {
		User user = new User();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		conn = getConn(true);
		String strSQL = "SELECT nickName FROM tms_personnel WHERE userID=?";
		ps = conn.prepareStatement(strSQL);
		ps.setString(1, str);
		rs = ps.executeQuery();
		
		if (rs.next()){
			user.setUserID(str);
			user.setNickName(rs.getString("nickName"));
		} else {
			strSQL = "SELECT userID FROM tms_personnel WHERE nickName=?";
			ps = conn.prepareStatement(strSQL);
			ps.setString(1, str);
			rs = ps.executeQuery();
			
			if(rs.next()){
				user.setUserID(rs.getString("userId"));
				user.setNickName(str);
			} else {
				boolean isDuplicated = true;
				while (isDuplicated) {
					int tmp = (int) (Math.random()*1000000);
					strSQL = "SELECT userID FROM tms_personnel WHERE userID=?";
					ps = conn.prepareStatement(strSQL);
					ps.setInt(1, tmp);
					rs = ps.executeQuery();
					if (!rs.next()){
						String lastName = "";
						String firstName = "";
						if (str.length()==3){
							lastName = str.substring(0,1);
							firstName = str.substring(1,3);
						} else {
							lastName = str.substring(0,1);
							firstName = str.substring(1,2);
						}
						user.setUserID(String.valueOf(tmp));
						user.setNickName(str);
						strSQL = "INSERT INTO tms_personnel (userID,nickName,lastName,firstName) VALUES (?,?,?,?)";
						ps = conn.prepareStatement(strSQL);
						ps.setInt(1, tmp);
						ps.setString(2, str);
						ps.setString(3, lastName);
						ps.setString(4, firstName);
						ps.execute();
						isDuplicated = false;
					}
				}
			}	
		}
		
		if (rs!= null) {rs.close();}
		if (ps!= null) {ps.close();}
		if (conn!= null) {conn.close();}
		
		return user;
	}
	
	/**
	 * 取得資料庫連線
	 * 
	 * @param isWeb
	 *            判斷現在的執行環境是網站還是AP，以為決定是用JNDI，還是JDBC
	 * @return DB連線
	 */
	public Connection getConn(boolean isWeb) {
		Connection con = null;

		if (isWeb) {
			try {
				Context ctx = new InitialContext();
				String strLookup = "java:/env/jdbc/TMS";
				DataSource ds = (DataSource) ctx.lookup(strLookup);
				con = ds.getConnection();
			} catch (NamingException e) {
				System.out.println("建立連線時發生錯誤"+e.toString());
			} catch (SQLException e) {
				System.out.println("建立連線時發生錯誤"+e.toString());
			}
		} else {
			try {
				Class.forName("com.mysql.jdbc.Driver");
				// 註冊driver
				con = DriverManager.getConnection(
						"jdbc:mysql://localhost:3306/activiti", "p_activiti",
						"p_activiti1234");
			} catch (ClassNotFoundException e) {
				System.out.println("DriverClassNotFound :" + e.toString());
			} catch (SQLException x) {
				System.out.println("Exception :" + x.toString());
			}
		}

		return con;
	}

	/**
	 * 取得指定月份，目前可用的流水號，此函式會將既有的序號加一後回傳
	 * 若該月份目前尚未新建，則會自動新增一筆該月份為1的記錄進去
	 * 
	 * @param isWeb	判斷現在的執行環境是網站還是AP，以為決定是用JNDI，還是JDBC
	 * @param type 判斷是要取那個情境的流水號
	 * @return
	 * @throws SQLException
	 */
	public int getSNandAdd(String type, boolean isWeb) throws SQLException {

		int sn =1;
		Calendar calendar = Calendar.getInstance(); // 創建一個Calendar實體
		int year = calendar.get(Calendar.YEAR);
		int month = calendar.get(Calendar.MONTH)+1;

		String strSQL_S = "SELECT sn FROM tms_sn WHERE year ="+year+" AND month ="+month+" AND type ='"+type+"'";
		String strSQL = "";
		
		Connection conn = getConn(isWeb);
		PreparedStatement ps = conn.prepareStatement(strSQL_S);
		
		ResultSet rs = ps.executeQuery();
		
		if (rs.next()) {
			sn = rs.getInt("sn");
			strSQL ="UPDATE tms_sn SET sn='" + (rs.getInt("sn") + 1)
					+ "' WHERE year=" + year + " AND month=" + month + " AND type='" + type +"'";
		} else {
			if (month==12) {
				month = 1;
				year = year +1;
			} else {
				month = month +1;
			}
			strSQL ="INSERT INTO activiti.tms_sn (`year`, month, type, sn) VALUES ('"+year+"', "+month+", '"+type+"',"+sn+")";
		}
		ps.execute(strSQL);
		

		rs.close();
		ps.close();
		conn.close();

		return sn;
	}
	
	/**
	 * 建立日期：2012/6/14  下午1:36:54
	 * 建立者： Arthur (郭啟揚)
	 * 目的： 取得指定的tpid, mrid下的當前版次
	 *
	 */
	public int getVer(int tpid,String mrid,Connection conn,PreparedStatement ps,ResultSet rs) throws SQLException{
		int ver =0;
		String strSQL = "SELECT MAX(ver) FROM tms_mrtcmapping WHERE tpid = ? AND mrid = ?";
		ps = conn.prepareStatement(strSQL);
		ps.setInt(1, tpid);
		ps.setString(2, mrid);
		rs = ps.executeQuery();
		rs.next();
		ver = rs.getInt(1);
		
		return ver;
	}
	
	/**
	 * 建立日期：2012/6/14  下午2:51:27
	 * 建立者： Arthur (郭啟揚)
	 * 目的：取得目前檔案上傳的流水號跑到多少了
	 *
	 */
	public int getFileUploadSN(Connection conn,PreparedStatement ps,ResultSet rs) throws SQLException{
		int sn =0;
		String strSQL = "SELECT MAX(sn) FROM tms_mrfileupload";
		ps = conn.prepareStatement(strSQL);
		rs = ps.executeQuery();
		rs.next();
		sn = rs.getInt(1);
		
		return sn;
	}
	
	/**
	 * 解析TCID 建立階層
	 * 
	 * @param tcid	要解析階層的字串
	 * @param uid	這次紀錄階層的始作俑都 UID
	 * @param isWeb	判斷現在的執行環境是網站還是AP
	 * @return 執行結果的字串，OK為成功，若有Error就直接回傳
	 */
	public String insertHierachy(String tcid, long uid, boolean isWeb) {

		String result = "";

		String[] strArray = tcid.split("_");
		Connection conn = getConn(isWeb);

		String strSQL = "";
		Statement stat = null;
		try {
			stat = conn.createStatement();

			// 起碼長度是2，才表示字串中有被寫入_
			if (strArray.length > 1) {
				for (int i = 0; i < strArray.length; i++) {
					strSQL = "INSERT INTO tms_tchierarchy (sn,hierarchy,parentHierarchy,uid,val) VALUES (NULL,"
							+ (i + 1)
							+ ","
							+ i
							+ ","
							+ uid
							+ ",'"
							+ changeCode(strArray[i]) + "')";
					stat.execute(strSQL);
				}
			}
			stat.close();
			conn.close();

		} catch (SQLException e) {
			result = e.toString();
		}

		result = "OK";

		return result;
	}
	
	
	/** 儲存對MR的意見 
	 * @throws SQLException **/
	public void keepCommentMR(int tpid,String mrid,int ver,String comment,int type,String creator,String role, Connection conn, PreparedStatement ps) throws SQLException{
		
		String strSQL = "INSERT INTO tms_mrcomment (sn,tpid,mrid,ver,comment,type,creator,creatorGroup,createdTime) VALUES (NULL,?,?,?,?,?,?,?,?)";
	    
		ps = conn.prepareStatement(strSQL);
		ps.setInt(1, tpid);
		ps.setString(2, mrid);
		ps.setInt(3, ver);
		ps.setString(4, comment);
		ps.setInt(5, type);
		ps.setString(6, creator);
		ps.setString(7, role);
		ps.setTimestamp(8, currentTimeStamp());
		ps.execute();
	    
	}
	
	/**
	 * 建立日期：2012/6/29 上午9:59:06
	 * 建立者：Arthur (郭啟揚)
	 * 目的：對執行的動作進行紀錄
	 * @param type 要記錄的動作類型，可參見tms_acttype Table
	 */
	public boolean keeplog(int tpid,String mrid,int ver,int type,String creator,Connection conn, PreparedStatement ps) throws SQLException {
		boolean res = true;
		String strSQL = "INSERT INTO tms_actlog (sn,tpid,mrid,ver,actSN,creator,createdDate)VALUES(NULL,?,?,?,?,?,?)";
	    
		ps = conn.prepareStatement(strSQL);
		ps.setInt(1, tpid);
		ps.setString(2, mrid);
		ps.setInt(3, ver);
		ps.setInt(4, type);
		ps.setString(5, creator);
		ps.setTimestamp(6, currentTimeStamp());
		res = ps.execute();
		
		return res;
	}
	
	
	public void keepLogTC(String tcID,long uID,String creator,int type,Connection conn, PreparedStatement ps) throws SQLException {
		String strInsertActLog = "INSERT INTO tms_actionlog (sn,tcID,uID,empID,actionID,CreatedDateTime) VALUES (NULL,?,?,?,?,?)";
		ps = conn.prepareStatement(strInsertActLog);
		ps.setString(1, tcID);
		ps.setLong(2, uID);
		ps.setString(3, creator);
		ps.setInt(4, type);
		ps.setTimestamp(5, currentTimeStamp());
		ps.execute();
	}
	
	
	/**
	 * 建立日期：2012/7/4 上午11:37:07
	 * 建立者：Arthur (郭啟揚)
	 * 目的：對人員的操作記載Log
	 *
	 */
	public void keepLogUser(String userID,String creator,int type,Connection conn, PreparedStatement ps) throws SQLException {
		String strInsertActLog = "INSERT INTO tms_actlog_user (userid,creator,actSN,createdDate) VALUES (?,?,?,?)";
		ps = conn.prepareStatement(strInsertActLog);
		ps.setString(1, userID);
		ps.setString(2, creator);
		ps.setInt(3, type);
		ps.setTimestamp(4, currentTimeStamp());
		ps.execute();
	}
	
	/**
	 * 建立日期：2012/6/14  下午1:52:59
	 * 建立者： Arthur (郭啟揚)
	 * 目的：回傳依指定格式下，目前的日期與時間
	 *
	 */
	public Timestamp currentTimeStamp(){
		java.util.Date dt = new java.util.Date();
	    java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	    String currentTime = sdf.format(dt);
	    return Timestamp.valueOf(currentTime);
	}
	
	/**
	 * 建立日期：2012/6/20 下午5:50:27
	 *  建立者 ：Arthur (郭啟揚)
	 *   目 的 ：取得指定MR項目的訊息
	 *
	 */
	public MRBody getMRbody(String yymm,String mrid,Connection conn,PreparedStatement ps,ResultSet rs) {
		MRBody mrBody = new MRBody();
		
		
		return mrBody;
	}
	

	public void deleteOwner(int tpid,String mrid,String ownerType,Connection conn,PreparedStatement ps,ResultSet rs) throws SQLException {
		if ("require".equalsIgnoreCase(ownerType)) {
			ownerType += "log";
		}
		String strSQL = "DELETE FROM activiti.tms_mritem_"+ownerType+" WHERE tpid=? AND mrid=?";
		ps = conn.prepareStatement(strSQL);
		ps.setInt(1, tpid);
		ps.setString(2, mrid);
		ps.execute();
	}
	
	public void insertOwner(int tpid,String mrid,String ownerType,String userID,Connection conn,PreparedStatement ps,ResultSet rs) throws SQLException {
		String strSQL = "INSERT INTO activiti.tms_mritem_"+ownerType+ "(tpid, mrid, userID) VALUES (?,?,?)";
		ps = conn.prepareStatement(strSQL);
		ps.setInt(1, tpid);
		ps.setString(2, mrid);
		ps.setString(3, userID);
		ps.execute();
	}
	
	/*	記錄錯誤訊息，以供日後偵錯	*/
	public String keepErrorMes (Connection conn, PreparedStatement ps,Exception e, String des, boolean isDisplay)  {
		
		String mes = e.toString() + "\n\n";
		
		for (int i=0; i<e.getStackTrace().length;i++) {
			mes += "檔案名稱："+e.getStackTrace()[i].getFileName() + "\n";
			mes += "所在行數："+e.getStackTrace()[i].getLineNumber() + "\n";
			mes += "方法名稱："+e.getStackTrace()[i].getMethodName() + "\n";
			mes += "類別名稱："+e.getStackTrace()[i].getClassName() + "\n\n";
		}
		
		if ( isDisplay ){ System.out.print(mes);}
		
		try {
			ps = conn.prepareStatement("INSERT INTO errmes (errMes,des) VALUES (?,?)");
			ps.setString(1, mes);
			ps.setString(2, des);
			ps.execute();
		} catch (SQLException e1) {
			System.out.println(errProcess(e1));
		}
		return mes;
	}
	
	public String errProcess (Exception e) {
		String mes = "";
	    mes += e.toString() + "\n\n";
		for (int i=0; i<e.getStackTrace().length;i++) {
			mes += "檔案名稱："+e.getStackTrace()[i].getFileName() + "\n";
			mes += "所在行數："+e.getStackTrace()[i].getLineNumber() + "\n";
			mes += "方法名稱："+e.getStackTrace()[i].getMethodName() + "\n";
			mes += "類別名稱："+e.getStackTrace()[i].getClassName() + "---\n";
		}
		
		return mes;
	}
	
	
}
